package cn.vastbase.jdbc.plsql;

import org.junit.Test;

import java.io.IOException;
import java.io.OutputStreamWriter;
import java.io.UnsupportedEncodingException;
import java.io.Writer;
import java.sql.*;

/**
 * <p> 存储过程示例 </p>
 * <p>可用于Vastbase G100 2.2 Build 15.4 MySQL兼容模式 </p>
 */
public class ProcTestCase {

    /* 定义连接对象 */
    static Connection conn = null;
    static String cname = "org.postgresql.Driver";
    static String url = "jdbc:postgresql://172.16.55.175:5432/zxs";
    static String username = "zxs";
    static String passwd = "Vastdata#0730";
    static CallableStatement cs = null;
    static Statement stmt = null;
    /* 定义结果集对象 */
    static ResultSet rs = null;

    /**
     * 普通存储过程
     */
    @Test
    public void commonProc() {
        /* 初始化连接 */
        if (conn() == null)
            return;
        try {
            stmt = conn.createStatement();
            String proc = "CREATE OR REPLACE FUNCTION add_numbers(a INT, b INT)\n" +
                    "     RETURNS INT AS $$\n" +
                    "     BEGIN\n" +
                    "     RETURN a + b;\n" +
                    "     END;\n" +
                    "     $$ LANGUAGE plpgsql;";
            stmt.execute(proc);
            System.out.println(proc);
            cs = conn.prepareCall("{ ? = CALL add_numbers(?, ?) }");
            cs.setInt(2, 10); // 输入参数a
            cs.setInt(3, 20); // 输入参数b
            cs.registerOutParameter(1, java.sql.Types.INTEGER); // 输出参数
            cs.execute();
            int result = cs.getInt(1); // 获取输出参数的值
            System.out.println("10 + 20 = " + result);
        } catch (SQLException e) {
            System.out.println("[FAIL] execute sql fail." + e.getMessage());
        } finally {
            close();
        }
    }


    /*
     CREATE TABLE eom.xml_info_201712 (
         done_code number NOT NULL,
         activity_way number NOT NULL,
         actioncode number NOT NULL,
         xml_data clob NULL
     );
     oracle 能直接更新结果集：clob.setString(1, "hello word");
     */
    @Test
    public void clobProc() throws SQLException {
        cname = "org.panweidb.Driver";
        url = "jdbc:panweidb://172.16.55.175:5000/test";

//        cname = "oracle.jdbc.driver.OracleDriver";
//        url = "jdbc:oracle:thin:@//172.16.55.145:1521/U12102";

        try {
            if (conn() == null)
                return;
            conn.setAutoCommit(false);
            String sql = "BEGIN INSERT INTO xml_info_201712 (done_code,activity_way,actioncode,xml_data) " +
                    "values (1,1,1,empty_clob()) returning xml_data into ?; END;";
            cs = conn.prepareCall(sql);
            cs.registerOutParameter(1, java.sql.Types.CLOB);
            cs.executeUpdate();

            Clob clob = cs.getClob(1);
            Writer writer = new OutputStreamWriter(clob.setAsciiStream(1), "UTF-8");
            writer.write("hello world");
            writer.close();

            clob.setString(1, "hello word");
            conn.commit();
            System.out.println(clob.getSubString(1, (int) clob.length()));
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (UnsupportedEncodingException e) {
            throw new RuntimeException(e);
        } catch (IOException e) {
            throw new RuntimeException(e);
        } finally {
            close();
        }
    }

    /**
     * setof 返回结果集
     */
    @Test
    public void setOfProc() {
        if (conn() == null)
            return;
        try {
            stmt = conn.createStatement();
            String proc = "CREATE OR REPLACE FUNCTION setoffunc(i int, out result_1 bigint, out result_2 bigint)\n" +
                    "    returns SETOF RECORD\n" +
                    "    as $$\n" +
                    "    begin\n" +
                    "        result_1 = i + 1;\n" +
                    "        result_2 = i * 10;\n" +
                    "    return next;\n" +
                    "    end;\n" +
                    "    $$language plpgsql;";
            System.out.println(proc);
            stmt.execute(proc);
            rs = stmt.executeQuery("SELECT * FROM setoffunc(1)");
            while (rs.next()) {
                Object column1 = rs.getInt(1);
                Object column2 = rs.getInt(2);
                System.out.println("| " + column1 + " | " + column2 + " |");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close();
        }
    }


    /**
     * <p>refcursor 返回游标</p>
     * 注意： 2.2.15.2版本 B兼容模式执行报错：execute sql fail.ERROR: syntax error at or near ""<unnamed portal 1>""
     */
    @Test
    public void refcursorProc() {
        if (conn() == null)
            return;
        try {
            stmt = conn.createStatement();
            String proc = "CREATE OR REPLACE FUNCTION refcursorfunc() RETURNS refcursor AS $$  DECLARE  mycurs refcursor; "
                    + " BEGIN "
                    + " OPEN mycurs FOR SELECT 1 UNION SELECT 2; "
                    + " RETURN mycurs; "
                    + " END;$$ ";
            System.out.println(proc);
            stmt.execute(proc);

            // 游标必须在事务内
//            conn.setAutoCommit(false);
            cs = conn.prepareCall("{? = call refcursorfunc()}");
            cs.registerOutParameter(1, Types.OTHER);
            cs.execute();
            ResultSet rs = (ResultSet) cs.getObject(1);
            while (rs.next()) {
                System.out.println(rs.getObject(1));
            }
            conn.commit();
        } catch (SQLException e) {
            System.out.println("[FAIL] execute sql fail." + e.getMessage());
        } finally {
            close();
        }
    }

    /**
     * 获取存储过程元数据
     */
    @Test
    public void getProcMetalData() {
        if (conn() == null)
            return;
        try {
            DatabaseMetaData metaData = conn.getMetaData();
            rs = metaData.getProcedureColumns("zxsdb", "public", "add_numbers", null);
            ResultSetMetaData rm = rs.getMetaData();
            int columnCount = rm.getColumnCount();
            while (rs.next()) {
                for (int i = 1; i <= columnCount; i++) {
                    System.out.println("COLUMN_NAME：" + rm.getColumnName(i) + ", COLUMN_VALUE：" + rs.getString(i));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close();
        }
    }


    /**
     *
     *
     */
    public void proc(){

    }




    /*

     */
    @Test
    public void proc2() throws SQLException {
        cname = "org.postgresql.Driver";
        url = "jdbc:postgresql://192.168.56.11:6000/test";
        username = "test";
        passwd = "Vastdata#0730";
        if (conn() == null)
            return;
        cs = conn.prepareCall("CALL JSPCMAX ('@F_YWRQ@-','1003','20231220')");
        cs.execute();
        rs = cs.getResultSet();
        while (rs.next()) {
            Object object = rs.getObject(1);
            System.out.println(object);
        }
    }


    public Connection conn() {
        try {
            Class.forName(cname);
            return conn = DriverManager.getConnection(url, username, passwd);
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
        return null;

    }

    public void close() {
        try {
            /* 关闭结果集对象 */
            if (rs != null)
                rs.close();
        } catch (SQLException e) {
            System.out.println("[FAIL] close resultSet fail." + e.getMessage());
        }
        try {
            /* 关闭语句对象 */
            if (cs != null)
                cs.close();
        } catch (SQLException e) {
            System.out.println("[FAIL] close callableStatement fail." + e.getMessage());
        }
        try {
            /* 关闭语句对象 */
            if (stmt != null)
                stmt.close();
        } catch (SQLException e) {
            System.out.println("[FAIL] close statement fail." + e.getMessage());
        }
        try {
            if (conn != null)
                conn.close();
        } catch (SQLException e) {
            System.out.println("[FAIL] close connection fail." + e.getMessage());
        }
    }

}
